**************************************************

*			CLEANING OF DCNR DATA 	        *

**************************************************

clear
set more off
*cd "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*PC*/
cd "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*Home PC*/ 
use EDWIN_Plats_Full.dta

	destring acres groundelevation latitudetrue latitudedec longitudetrue longitudedec legacywislatitudedec legacywislongitudedec quadranglesectionnumber totalmeasureddepthfromft totalmeasureddepthtoft stratigraphiccode deepestproducingdepthft gasflownaturalmcf oilflownaturalbbl pressurenaturalpsi pressurenaturalhr gasflowaftertreatmentmcf oilflowaftertreatmentbbl pressureaftertreatmentpsi pressureaftertreatmenthr, replace
	drop targetproducingformation targetformation bottomholelatitudetrue bottomholelatitudedec bottomholelongitudetrue bottomholelongitudedec anticipatedtrueverticaldepthfrom anticipatedtrueverticaldepthtoft anticipatedtotalmeasureddepthfro anticipatedtotalmeasureddepthtof ermitnumber crapinumber submissiontype radioactivetracersused specifytracer match_cat
	drop depid datasource

********************CREATING A MINIMUM AND MAXIMUM YEAR FOR THE DCNR DATA********************
	replace crreceiptdate="" if crreceiptdate== "1/1/1901"		/*1/1/1901 means that the date is unknown. Therefore these wells do not have dates for these fields.*/
	replace receiptdate="" if receiptdate== "1/1/1901"
	replace stimulationcompletiondate="" if stimulationcompletiondate== "1/1/1901"
	
	gen crreceipt_year=substr(crreceiptdate, -4,.)
	sort crreceipt_year
	destring crreceipt_year, replace

	gen receiptdate_year=substr(receiptdate, -4,.) /*Receipt date is when the record was entered into the DCNR system*/
	sort receiptdate_year
	destring receiptdate_year, replace

	gen stimulationcompletiondate_year=substr(stimulationcompletiondate, -4,.)
	sort stimulationcompletiondate_year
	destring stimulationcompletiondate_year, replace
	replace stimulationcompletiondate_year=. if stimulationcompletiondate_year<1850

	egen minyear= rowmin(crreceipt_year receiptdate_year stimulationcompletiondate_year)
	egen maxyear= rowmax(crreceipt_year receiptdate_year stimulationcompletiondate_year)

*************DROPING DUPLICATE PERMITS,KEEPING BASED ON WELL STATUS********************
	gen permit_num=substr(permitnumber,4,9)
	replace wellstatus= upper(wellstatus)
	replace wellstatus= "PLUGGED & ABANDONED" if wellstatus== "PLUGGED AND ABANDONED"
	replace wellstatus="" if wellstatus== "."
	replace wellstatus= "ACTIVE" if wellstatus== "OIL"
	replace wellstatus= "A" if wellstatus== "UNDEFINED"
	replace wellstatus= "C" if wellstatus== "ACTIVE"
	replace wellstatus= "B" if wellstatus== "INACTIVE"
	replace wellstatus= "D" if wellstatus== "ABANDONED"
	replace wellstatus= "E" if wellstatus== "PLUGGED & ABANDONED"
	replace event= "FULLY PLUGGED" if event=="Fully Plugged"
	replace wellstatus= "E" if event== "FULLY PLUGGED"
	replace event= "Z" if event=="FULLY PLUGGED"
	gsort permit_num -minyear
	by permit_num: replace minyear = minyear[_N]
	replace maxyear=0 if maxyear==.
	sort permit_num maxyear
	by permit_num: replace maxyear = maxyear[_N]
	replace maxyear=. if maxyear==0
	gsort permit_num -wellstatus -event minyear -maxyear
	duplicates drop permit_num, force /*Dropping duplicate permitnum observations, assuming the following order as the "most recent" well status: (Plugged, Abandoned, Active, Inactive)*/
	replace wellstatus= "UNDEFINED" if wellstatus== "A"
	replace wellstatus= "ACTIVE" if wellstatus== "C"
	replace wellstatus= "INACTIVE" if wellstatus== "B"
	replace wellstatus= "ABANDONED" if wellstatus== "D"
	replace wellstatus= "PLUGGED & ABANDONED" if wellstatus== "E"
	replace event= "FULLY PLUGGED" if event=="Z"
	
save EDWIN_Plats_Full_Clean.dta, replace

**************************************************

*		 CLEANING OF DEP PRODUCTION REPORT DATA  *

**************************************************
clear
set more off
*cd "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*PC*/
cd "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*Home PC*/ 
use DEP_Final_Dataset.dta  /*Data from DEP Production Reports*/

	replace well_status= "B" if well_status=="Active"
	replace well_status= "Y" if well_status=="Abandoned"
	replace well_status= "Inactive" if well_status=="Regulatory Inactive Status"
	replace well_status= "A" if well_status=="Inactive"
	replace well_status= "Z" if well_status=="Plugged OG Well"
	gsort permitnum year -well_status -prod
	duplicates drop permitnum year, force /*Dropping duplicate permitnum and year observations, assuming the following order as the "most recent" well status: (Plugged, Abandoned, Active, Inactive)*/
	replace well_status= "Active" if well_status=="B"
	replace well_status= "Inactive" if well_status=="A"
	replace well_status= "Abandoned" if well_status=="Y"
	replace well_status= "Plugged OG Well" if well_status=="Z"
	
	gen indicate=(well_status=="Plugged OG Well")
	gen indicate2=(well_status!="Plugged OG Well" & well_status!="")
	bysort permitnum (year) : 	gen indicate3= indicate2[_n-1]
	gen indicate4=indicate+indicate3
	gen date_plugg_year_drop=year if indicate4==2
	bysort permitnum: egen date_plugg_year_prod= max(date_plugg_year_drop)
	drop indicate* date_plugg_year_drop
	
	rename well_status wellstatus_prod
	bysort permitnum (year) : replace wellstatus_prod = wellstatus_prod[_n-1] if missing(wellstatus_prod)
	
	gen welltype_prod="Gas" if gasquantity>0 & gasquantity!=.
	replace welltype_prod="Oil" if oilquantity>0 & oilquantity!=.
	replace welltype_prod="Oil and Gas" if oilquantity>0 & oilquantity!=. & gasquantity>0 & gasquantity!=.
	bysort permitnum (year) : replace welltype_prod = welltype_prod[_n-1] if missing(welltype_prod) 
	
	rename spud_date spud_date_prod
	bysort permitnum (year) : replace spud_date_prod = spud_date_prod[_n-1] if missing(spud_date_prod)
	
	rename well_county county_prod
	bysort permitnum (year) : replace county_prod = county_prod[_n-1] if missing(county_prod) 
	
	rename well_latitude latitudedec_prod 
	bysort permitnum (year) : replace latitudedec_prod = latitudedec_prod[_n-1] if missing(latitudedec_prod) 
	rename well_longitude longitudedec_prod
	bysort permitnum (year) : replace longitudedec_prod = longitudedec_prod[_n-1] if missing(longitudedec_prod)
	
	rename unconventional unconventi_prod
	replace unconventi_prod ="Y" if unconventi_prod=="Yes"
	replace unconventi_prod ="N" if unconventi_prod=="No"
	bysort permitnum (year) : replace unconventi_prod = unconventi_prod[_n-1] if missing(unconventi_prod)
	
	rename operator operator_prod
	bysort permitnum (year) : replace operator_prod = operator_prod[_n-1] if missing(operator_prod) 
	
	replace prod=. if prod==0
	gen year_prod= year*prod
	bysort permitnum: egen last_produ_report_year= max(year_prod)
	bysort permitnum: egen first_produ_report_year= min(year_prod)
	
	gen year_since= 2017-last_produ_report_year

	replace year_since=37 if year_since==.
	gen never_report=(year_since==37)
	gsort permitnum -year
	duplicates drop permitnum, force
	
	rename permitnum permit_num
	replace welltype_prod=welltype  if welltype_prod==""
	keep permit_num wellstatus_prod spud_date_prod county_prod latitudedec_prod longitudedec_prod unconventi_prod welltype_prod operator_prod year_since never_report last_produ_report_year first_produ_report_year date_plugg_year_prod
	
	
	foreach var of varlist wellstatus_prod spud_date_prod county_prod latitudedec_prod longitudedec_prod unconventi_prod welltype_prod operator_prod year_since never_report last_produ_report_year first_produ_report_year  date_plugg_year_prod {
     label variable `var' "FROM DEP PRODUCTION REPORTS"
	}

save DEP_Production_Clean.dta, replace

**************************************************

*		    CLEANING OF DEP WELL DATABASE DATA   *

**************************************************
clear
*cd "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*PC*/
cd "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*Home PC*/ 
import delimited DEP_WELLS.csv   /*Data from DEP Well Database*/

	duplicates tag permit_num, gen(dup)
	tab dup								/*No Duplicates in Washington County in the DEP Data*/

	foreach var of varlist well_name operator operator_n well_type well_type_ well_statu well_sta_1 permit_dat permit_d_1 spud_date conservati municipali municipa_1 municipa_2 latitude longitude prmry_fid unconventi surface_el well_confi coal_ind well_pad_n date_plugg well_pad_i storage_fi site_id site_name lat lng {
     label variable `var' "FROM DEP WELL DATABASE"
	}

save DEP_Wells_Clean.dta, replace

**************************************************

*		     MERGING IN DEP DATA &				 *
*		CLEANING TO MAKE THE THREE DATASETS      *

**************************************************
clear	
*cd "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*PC*/
cd "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*Home PC*/ 
use EDWIN_Plats_Full_Clean.dta

	merge 1:1 permit_num using DEP_Wells_Clean.dta
	rename _merge merge1
	merge 1:1 permit_num using DEP_Production_Clean.dta
	replace year_since=37 if year_since==.
	replace never_report=1 if year_since==37

*******************************WELL TYPE***************************************
	rename welltype welltype1
	gen welltype = upper(welltype1)
	drop welltype1
	replace welltype = well_type if welltype=="UNDETERMINED" | welltype=="" | welltype=="TEMPORARILY UNSPECIFIED"| welltype=="TEMPORARILY UNSPECIF"| welltype=="UNKNOWN" | welltype=="O"| welltype=="." | welltype=="OBSERVATION" | welltype=="UNDEFINED" | welltype=="TEST WELL" | welltype=="MULTIPLE WELL BORE TYPE" | welltype==" " 
	replace welltype = welltype_prod if welltype=="UNDETERMINED" | welltype=="" | welltype=="TEMPORARILY UNSPECIFIED"| welltype=="TEMPORARILY UNSPECIF"| welltype=="UNKNOWN" | welltype=="O"| welltype=="." | welltype=="OBSERVATION" | welltype=="UNDEFINED" | welltype=="TEST WELL" | welltype=="MULTIPLE WELL BORE TYPE" | welltype==" " 
	drop well_type well_type_ welltype_prod
	rename welltype welltype1
	gen welltype = upper(welltype1)
	drop welltype1

	replace welltype = "OIL AND GAS" if welltype=="COMB. OIL&GAS"
	replace welltype = "OIL AND GAS" if welltype=="OIL & GAS"
	replace welltype = "UNDEFINED" if welltype=="MULTIPLE WELL BORE TYPE"
	replace welltype = "STORAGE" if welltype=="STORAGE WELL"
	replace welltype = "COALBED METHANE" if welltype=="COAL BED METHANE"
	replace welltype = "UNDEFINED" if welltype=="UNDETERMINED"
	replace welltype = "UNDEFINED" if welltype==""
	replace welltype = "UNDEFINED" if welltype==" "
	replace welltype = "UNDEFINED" if welltype=="TEMPORARILY UNSPECIFIED"
	replace welltype = "UNDEFINED" if welltype=="TEMPORARILY UNSPECIF"
	replace welltype = "UNDEFINED" if welltype=="UNKNOWN"
	replace welltype = "UNDEFINED" if welltype=="O"
	replace welltype = "UNDEFINED" if welltype=="."
	replace welltype = "UNDEFINED" if welltype=="OBSERVATION"
	replace welltype = "UNDEFINED" if welltype=="UNDETERMINED"
	replace welltype = "DRY HOLE" if welltype=="DRY"
	replace welltype = "INCOMPLETE" if welltype=="JUNKED"
	replace welltype = "WATER DISPOSAL" if welltype=="DISPOSAL"
	replace welltype = "WATER DISPOSAL" if welltype=="WASTE DISPOSAL"
	replace welltype = "WATER DISPOSAL" if welltype=="WATER INTAKE"
	replace welltype = "UNDEFINED" if welltype=="CORE"	

*******************************WELL STATUS***************************************
	replace wellstatus = well_statu if wellstatus=="" | wellstatus=="." /*Giving 'wellstatus' (From EDWIN first priority to lable a well's status. Giving DEP database's well_statu second priority, and 'wellstatus_prod' from the production reports third priority. [The same was done above with well type]*/
	replace wellstatus = wellstatus_prod if wellstatus=="" | wellstatus=="."

	rename wellstatus wellstatus1
	gen wellstatus = upper(wellstatus1)
	drop wellstatus1

	replace wellstatus= "NOT DRILLED" if wellstatus== "OPERATOR REPORTED NOT DRILLED"
	replace wellstatus= "NOT DRILLED" if wellstatus== "PROPOSED BUT NEVER MATERIALIZED"
	replace wellstatus= "INACTIVE" if wellstatus== "REGULATORY INACTIVE STATUS"
	replace wellstatus= "PLUGGED & ABANDONED" if wellstatus== "PLUGGED OG WELL"
	replace wellstatus= "PLUGGED & ABANDONED" if event=="FULLY PLUGGED"
	replace wellstatus= "DEP PLUGGED" if well_statu=="DEP Plugged" /*If listed as plugged anywhere in DEP data, overwriting EDWIN as plugged*/
	replace wellstatus= "PLUGGED & ABANDONED" if well_statu=="Plugged OG Well" /*If listed as plugged anywhere in DEP data, overwriting EDWIN as plugged*/
	replace wellstatus= "DEP PLUGGED" if wellstatus_prod=="DEP Plugged" /*If listed as plugged anywhere in DEP data, overwriting EDWIN as plugged*/
	replace wellstatus= "PLUGGED & ABANDONED" if wellstatus_prod=="Plugged OG Well" /*If listed as plugged anywhere in DEP data, overwriting EDWIN as plugged*/
	replace wellstatus= "PLUGGED & ABANDONED" if regulatorystatus=="Plugged and Abandoned"  /*If listed as plugged anywhere in DEP data, overwriting EDWIN as plugged*/
	replace wellstatus= "NOT RECORDED" if wellstatus== ""
	replace wellstatus= "NOT RECORDED" if wellstatus== "UNDEFINED"
	
	drop well_statu well_sta_1 wellstatus_prod

	gen wellstatus_short= wellstatus
		replace wellstatus_short= "ABANDONED" if wellstatus== "DEP ABANDONED LIST"
		replace wellstatus_short= "ABANDONED" if wellstatus== "DEP ORPHAN LIST"
		replace wellstatus_short= "PROPERLY PLUGGED & ABANDONED" if wellstatus== "DEP PLUGGED"
		replace wellstatus_short= "PROPERLY PLUGGED & ABANDONED" if wellstatus== "PLUGGED & ABANDONED" 

	gen plugged= (wellstatus_short=="PROPERLY PLUGGED & ABANDONED")
	gen reporting_production= (year_since==0)
	
********************STANDARDIZING "EVENT"********************
	rename event event1
	gen event= upper(event1)
	drop event1
	replace event="NOT SPECIFIED" if event==""
	
********************STANDARDIZING "regulatorystatus"********************
	replace regulatorystatus = "Cancelled" if regulatorystatus=="CN"
	replace regulatorystatus = "Valid" if regulatorystatus=="VA"
	replace regulatorystatus = "Expired" if regulatorystatus=="EX"
	replace regulatorystatus = "OTHER" if regulatorystatus=="OR"
	replace regulatorystatus = "OTHER" if regulatorystatus=="Other"
	replace regulatorystatus = "Void" if regulatorystatus=="VO"
	replace regulatorystatus = "OTHER" if regulatorystatus=="RV"
	replace regulatorystatus = "OTHER" if regulatorystatus=="VA;OD"
	replace regulatorystatus = "OTHER" if regulatorystatus=="."
	rename regulatorystatus regulatorystatus1
	gen regulatorystatus= upper(regulatorystatus1)
	drop regulatorystatus1
	
*******************************FURTHER CLEANING***************************************
	replace operatorname= operator if operatorname==""| operatorname=="UNKNOWN OPR"| operatorname=="UNDEFINED"| operatorname=="UNKNOWN"| operatorname=="Unknown"| operatorname=="Unknown Opperator"| operatorname=="Unknown Opr"| operatorname=="UNKNOWN OPERATOR" | operatorname==" "
	replace operatorname= operator_prod if operatorname==""| operatorname=="UNKNOWN OPR"| operatorname=="UNDEFINED"| operatorname=="UNKNOWN"| operatorname=="Unknown"| operatorname=="Unknown Opperator"| operatorname=="Unknown Opr"| operatorname=="UNKNOWN OPERATOR"| operatorname==" "
	replace operatorname="UNKNOWN" if operatorname==""
	drop operator operator_n operator_prod
	replace municipality = municipali if municipality==""
	drop municipali municipa_1 municipa_2
	drop lat lng
	replace latitudedec = latitude if latitudedec==.
	replace longitudedec = longitude if longitudedec==.
	replace latitudedec=latitudedec_prod if latitudedec==.
	replace longitudedec=longitudedec_prod if longitudedec==.
	drop latitude longitude latitudedec_prod longitudedec_prod

	replace farmname = well_name if farmname==""
	replace farmname = site_name if farmname==""
	replace farmname = well_pad_n if farmname==""
	drop well_name site_name well_pad_n 
	 
	replace unconventi=unconventi_prod if unconventi==""
	drop unconventi_prod
	
	replace county=county_prod if county==""
	rename county county1
	gen county= upper(county1)
	drop county1
	replace county="MCKEAN" if county=="MC KEAN"
	
*******************************CREATING A MINIMUM AND MAXIMUM YEAR FROM THE DATES FOUND IN THE THREE DATASETS***************************************
	replace spud_date= "" if spud_date=="1800-01-01 00:00:00"
	replace spud_date="" if spud_date== "1/1/1901"
		*br spud_date_prod if spud_date_prod!="" /*We cannot use this variable to define spud date because it only contain two digit years, which cannot be distinguished across decades. These dates were also inconsistent in many cases with dates in the other two sources.*/
	replace permit_dat="" if permit_dat== "1/1/1901"
	replace date_plugg="" if date_plugg== "1/1/1901"
	replace date_plugg="" if plugged==0
	
	gen spud_date_year=substr(spud_date, -4,.)
	replace spud_date_year="" if spud_date_year=="0:00"
	destring spud_date_year, replace
	replace spud_date_year=. if spud_date_year>2018

	gen permit_dat_year=substr(permit_dat, -4,.)
	destring permit_dat_year, replace
	replace permit_dat_year=. if permit_dat_year>2018
	
	
	gen date_plugg_year=substr(date_plugg, -4,.)
	destring  date_plugg_year, replace
	replace date_plugg_year=. if date_plugg_year>2018
	replace date_plugg_year=stimulationcompletiondate_year if event=="FULLY PLUGGED" & date_plugg_year==. 
			/*It is an assumption that the stimulation completion date refers to the plugging date if the event is "Fully Plugged"*/
	replace date_plugg_year= date_plugg_year_prod if date_plugg_year==. & date_plugg_year_prod!=.
		drop date_plugg_year_prod

		
	egen minyear2= rowmin(minyear last_produ_report_year first_produ_report_year spud_date_year permit_dat_year date_plugg_year)
	egen maxyear2= rowmax(maxyear last_produ_report_year first_produ_report_year spud_date_year permit_dat_year date_plugg_year)
	drop minyear
	drop maxyear
	rename minyear2 minyear
	rename maxyear2 maxyear
	
	drop permitnumber permitnumberold legacywislatitudedec legacywislongitudedec oldest_for target_for producing_ well_pad_l access_roa total_maxi reach_code huc_code uic_id storage_fi dup merge1 _merge
	order permit_num
	
	gen retroactive_test= substr(permit_num,-5,.)
	destring retroactive_test, replace force
	gen retroactive=1 if retroactive_test>89999 & retroactive_test<100000
	tab minyear retroactive
	replace minyear=1815 if minyear>1957 & retroactive==1 /*These wells are likely retroactive and need an earlier minyear. I am artificially classifying these wells as "1815" wells.*/
	replace minyear=1815 if minyear==. & unconventi!="Y" & wellstatus!="NOT DRILLED" & operatorname=="UNKNOWN" /*Since the operator is unknown, these wells must have been drilled before 1957*/

	tostring minyear, generate(minyear_str)
	gen minyear_dec= substr(minyear_str, 1,3)
	destring minyear_dec, replace
	gen minyear_decade= minyear_dec*10
	replace minyear_decade= 0 if minyear_decade==.
	drop minyear_dec
	
	label variable permit_num "Well Eight Digit Permit Number"
	label variable last_produ_report_year "Most Recent Year For Which Production Reported with DEP"
	label variable first_produ_report_year "Earliest Year For Which Production Reported with DEP"
	label variable year_since "Years Since Last Production Report was Filed With DEP"
	label variable never_report "1= The Well Never Reported Production in a DEP Report"
	label variable welltype "Well Type Listed by Either DEP or DCNR"
	label variable wellstatus "Well Status Listed by Either DEP or DCNR"
	label variable wellstatus_short "Simplified Well Status Variable"
	label variable plugged "1= The well has been listed as plugged by the operator or DEP"
	label variable reporting_production "1= Reported Production in Most Recent Period (2017)"
	label variable event "Event"
	label variable county "County"
	label variable date_plugg_year "Well's Listed Plug Date"
	label variable spud_date_year "Well's Listed Spud Date"
	label variable permit_dat_year "Well's Listed Permit Date"
	label variable minyear "Well's Minimum listed date across datasets"
	label variable maxyear "Well's Maximum listed date across datasets"
	label variable regulatorystatus "Regulatory Status of the Well"
	label variable retroactive "Well Was Permitted Retroactively"
	label variable minyear_decade "Decade of the Well's Minimum Date"
	                    
	save DEP_DCNR_COMBINED.dta, replace

*******************************************

*			EXPORT X Y FOR GIS			  *

*******************************************
clear
set more off
*cd "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*PC*/
cd "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data" /*Home PC*/ 
use DEP_DCNR_COMBINED.dta
	
*******************************DROPPING OBSERVATIONS THAT ARE NOT USEFUL RIGHT NOW***************************************

	keep if county=="WASHINGTON" /*Only looking at Washington County Data*/
	drop if permit_num=="125-00142" /*These permit numbers are not in Washington County*/
	drop if permit_num=="125-00264"
	drop if permit_num=="125-01005"
	drop if permit_num=="125-01959"
	drop if permit_num=="125-21900"
	drop if permit_num=="125-90274"
	drop if permit_num=="125-90286"
	
	drop if wellstatus== "NOT DRILLED"	
	drop if welltype== "INCOMPLETE"		 /*Dropping all permits that we know were not drilled or completed*/
	
	drop if latitudedec==0 				/*Dropping observations without x y coordinates*/
	drop if latitudedec==.
	drop if longitudedec==0
	drop if longitudedec==.
	
	drop if unconventi=="Y" | well_confi=="Horizontal Well" /*Dropping unconventional wells*/

	drop if minyear==. /*Do not have dates. Proceeding with conventional wells that 1) were not labled as "NOT DRILLED" or "INCOMPLETE" by DEP, and 2)Have lat/long coordinates */
		
	sort permit_num /*Generating Decade of plugging*/
	gen well_id= _n
	tostring  date_plugg_year, generate( date_plugg_year_str)
	gen date_plugg_dec= substr(date_plugg_year_str, 1,3)
	destring date_plugg_dec, replace
	gen date_plugg_decade= date_plugg_dec*10
	drop date_plugg_dec
	
	gen pre1955=(minyear<1955 & minyear!=.)
	
	keep well_id permit_num operatorname county latitudedec longitudedec welltype wellstatus wellstatus_short plugged year_since never_report reporting_production retroactive permit_dat_year spud_date_year date_plugg_year last_produ_report_year first_produ_report_year minyear maxyear minyear_decade event stimulationcompletiondate_year pre1955 deepestproducingdepthft totalmeasureddepthfromft totalmeasureddepthtoft
	order well_id permit_num operatorname county latitudedec longitudedec welltype wellstatus wellstatus_short plugged year_since never_report reporting_production retroactive permit_dat_year spud_date_year date_plugg_year last_produ_report_year first_produ_report_year minyear maxyear minyear_decade event stimulationcompletiondate_year pre1955 deepestproducingdepthft totalmeasureddepthfromft totalmeasureddepthtoft
	
	save DEP_DCNR_COMBINED_BackMerge1.dta, replace
	 
	keep well_id plugged minyear never_report permit_num latitudedec longitudedec
	
	export excel using "WashingtonCountyForGeoAllWells.xls", firstrow(variables) replace /*Export for Arc GIS X Y Data*/
	/*IMPORTANT NOTE: Tables from Stata/Excel are in the  GCS_North_American_1983 (NAD 1983) coordinate system, while the WC Shape Files are in NAD_1983_StatePlane_Pennsylvania_South_FIPS_3702*/
	/*You have to put the excel data into the GIS Table of contents, right click the sheet and select "DATA > EXPORT," export it as a File and Personal Geodatabase Table. Then you can right click that table in the Table of Contents
	and "display XY data," making sure to select the right coordinate system in the process (GCS_North_American_1983 (NAD 1983)). It will work only in this order of operations*/
	
	*Cleaning Up*
	erase DEP_DCNR_COMBINED.dta
	erase WashingtonCountyForGeoAllWells.xls
	erase EDWIN_Plats_Full_Clean.dta
	erase DEP_Production_Clean.dta
	erase DEP_Wells_Clean.dta
	*erase DEP_DCNR_COMBINED_BackMerge1.dta
